import openpyxl

from config.config import EXCEL_FILE


def read_excel():
    workbook=openpyxl.load_workbook(EXCEL_FILE)
    # worksheet=workbook["Sheet1"]

    data=[]
    all_cases=[]
    current_case=None
    for worksheet in workbook.worksheets:
        keys=[cell.value for cell in worksheet[2]]
        for row in worksheet.iter_rows(min_row=3,values_only=True):
            data_dict=dict(zip(keys, row))
            # print(data_dict)
            if data_dict["id"] is not None:
                current_case={
                    "id":data_dict["id"],
                    "feature":data_dict["feature"],
                    "story":data_dict["story"],
                    "title":data_dict["title"],
                    "steps":[{
                        "step_num":data_dict["step_num"],
                        "step_name":data_dict["step_name"],
                        "keyword": data_dict["keyword"],
                        "by": data_dict["by"],
                        "value": data_dict["value"],
                        "data": data_dict["data"],
                        "index": data_dict["index"]
                    }],
                    "is_true":data_dict["is_true"]
                }
                all_cases.append(current_case)
            elif current_case is not None:
               current_case["steps"].append({
                   "step_num": data_dict["step_num"],
                   "step_name": data_dict["step_name"],
                   "keyword": data_dict["keyword"],
                   "by": data_dict["by"],
                   "value": data_dict["value"],
                   "data": data_dict["data"],
                   "index": data_dict["index"]
               })
    data=[case for case in all_cases if case["is_true"]]
    # print(data)
    workbook.close()

    return data


